<?php 
class penugasan_followup_model extends CI_model{

	function list_teknisi(){
		$this->db->select('*');
		$this->db->from('teknisi');
		$this->db->order_by('teknisi.id_teknisi', 'DESC');
		$q = $this->db->get();
		return $q->result();
	}

	function list_kontak(){
		$this->db->select('*');
		$this->db->from('kontak');
		$q = $this->db->get();
		return $q->result();
	}


	function list_penugasan_marketing(){
		$q = $this->db->query("SELECT *, pm.id_perusahaan as pfid_perusahaan, pm.id_marketer as pfid_marketer
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   JOIN 
							   marketer m ON pm.id_marketer=m.id_marketer
							   WHERE p.status_marketing_perusahaan='positif' AND pm.penugasan_followup !=1 
							   ORDER BY pm.id_penugasan_marketing DESC");
		return $q->result();
	}

	function list_penugasan_marketing_search($keyword){
		$q = $this->db->query("SELECT *, pm.id_perusahaan as pfid_perusahaan, pm.id_marketer as pfid_marketer
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   JOIN 
							   marketer m ON pm.id_marketer=m.id_marketer
							   WHERE p.status_marketing_perusahaan='positif' AND pm.penugasan_followup<>1 AND p.nama_perusahaan LIKE '%$keyword%'
							   ORDER BY pm.id_penugasan_marketing DESC");
		return $q->result();
	}

	function show_penugasan_followup(){
		$q = $this->db->query("SELECT *
							   FROM penugasan_followup pf
							   JOIN 
							   (SELECT p.id_perusahaan,p.nama_perusahaan, p.status_marketing_perusahaan, pm.id_penugasan_marketing
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   ) m ON pf.id_penugasan_marketing=m.id_penugasan_marketing
							   JOIN
							   teknisi t ON pf.id_teknisi=t.id_teknisi
							   WHERE pf.id_teknisi IS NOT NULL
							   ORDER BY pf.id_penugasan_followup DESC");
		return $q->result();
	}

	function show_penugasan_followup_search($keyword){
		$q = $this->db->query("SELECT *
							   FROM penugasan_followup pf
							   JOIN 
							   (SELECT p.id_perusahaan,p.nama_perusahaan, p.status_marketing_perusahaan, pm.id_penugasan_marketing
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   ) m ON pf.id_penugasan_marketing=m.id_penugasan_marketing
							   JOIN
							   teknisi t ON pf.id_teknisi=t.id_teknisi
							   WHERE pf.id_teknisi IS NOT NULL AND m.nama_perusahaan LIKE '%$keyword%'
							   ORDER BY m.nama_perusahaan DESC");
		return $q->result();
	}

	function detail_penugasan_followup($id){
		$q = $this->db->query("SELECT *
							   FROM penugasan_followup pf
							   JOIN 
							   (SELECT p.id_perusahaan,p.nama_perusahaan, p.status_marketing_perusahaan, p.kategori_perusahaan, p.alamat_perusahaan, p.longitude_perusahaan, p.latitude_perusahaan, p.keterangan_perusahaan, pm.id_penugasan_marketing, pm.deskripsi_hasil_marketing, pm.id_marketer, m.nama_marketer
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   JOIN 
							   marketer m ON pm.id_marketer=m.id_marketer
							   ) m ON pf.id_penugasan_marketing=m.id_penugasan_marketing
							   JOIN
							   teknisi t ON pf.id_teknisi=t.id_teknisi
							   WHERE id_penugasan_followup=$id");
		return $q->result();
	}
}
?>